package com.sunlands.zlcx.usercenter.repository;

import com.sunlands.zlcx.usercenter.domain.UsersLearnTimingDO;
import com.sunlands.zlcx.usercenter.vo.PersonalSummaryDateVO;
import com.sunlands.zlcx.usercenter.vo.PersonalSummaryVO;
import com.sunlands.zlcx.usercenter.vo.PersonalTotalSummaryVO;
import com.sunlands.zlcx.usercenter.vo.count.LearnTimeChartVO;
import com.sunlands.zlcx.usercenter.vo.count.LearnTimeOverView;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

@Repository
public interface UsersLearnTimingRepository extends JpaRepository<UsersLearnTimingDO, Long>, JpaSpecificationExecutor<UsersLearnTimingDO> {

    @Query(value = "SELECT SUM(study_minutes) FROM tz_learn_timing WHERE TO_DAYS(count_time) = TO_DAYS((NOW())) and user_id =?1 ", nativeQuery = true)
    Long sumLearningTimeToday(Long userId);

    @Query(value = "select count(1) from (select count(1) from tz_learn_timing where user_id =?1 and YEARWEEK(date_format(count_time,'%Y-%m-%d')) = YEARWEEK(now()) group by date(count_time)) t", nativeQuery = true)
    Integer countLearningDaysWeek(Long userId);

    @Query(value = "SELECT sum(t.study_minutes) \n" +
            "FROM  tz_learn_timing t where t.user_id = ?1 and date_format(t.count_time, '%Y-%m') =  date_format(now(), '%Y-%m')", nativeQuery = true)
    Integer sumLearningTimeCurrentMonth(Long userId);

    @Query(value = "SELECT sum(t.study_minutes) \n" +
            "FROM  tz_learn_timing t where t.user_id = ?1 and date_format(t.count_time, '%Y-%m') =  ?2", nativeQuery = true)
    Integer sumLearningTimeByUserIdAndMonth(Long userId, String month);

    @Query(value = "SELECT SUM(t.`study_minutes`)\n" +
            "  FROM tz_learn_timing t,\n" +
            "       `tz_user_target_relation` tu\n" +
            " WHERE tu.user_id=?1\n" +
            "   AND t.target_id= tu.`target_id`\n" +
            "   AND t.`user_id`= tu.`user_id`\n" +
            "   AND tu.`status`= 1\n" +
            "   AND t.`count_time`> DATE_SUB(tu.`create_time`, INTERVAL 3 HOUR) ", nativeQuery = true)
    Long sumLearningTimeTarget(Long userId);

    @Query(value = "select end_time from tz_learn_timing where user_id = ?1 order by end_time desc limit 0,1", nativeQuery = true)
    Date getLastLearningEndTime(Long userId);

    @Query(value = "select * from tz_learn_timing where user_id = ?1 and status = ?2 order by create_time desc limit 0,1", nativeQuery = true)
    UsersLearnTimingDO findByUserIdAndStatusOne(Long userId, Integer status);

    @Query(value = "select id from tz_learn_timing where user_id = ?1 order by create_time desc limit 0,1", nativeQuery = true)
    Long findByUserIdOne(Long userId);

    @Query(value = "select * from tz_learn_timing where user_id = ?1 order by create_time desc limit 0,1", nativeQuery = true)
    UsersLearnTimingDO findDOByUserIdOne(Long userId);

    @Query(value = "update tz_learn_timing set learn_name=?1 where id=?2", nativeQuery = true)
    @Modifying
    @Transactional
    Integer updateLearnNameById(String learnName, Long id);

    /**
     * 查看单人当天总时长、总次数
     *
     * @param userId
     * @return
     */
    @Query(value = "select new com.sunlands.zlcx.usercenter.vo.PersonalSummaryVO(coalesce(sum(learn.studyMinutes),0), count(learn.userId)) from com.sunlands.zlcx.usercenter.domain.UsersLearnTimingDO learn where learn.userId=?1 and date(learn.countTime) = date(now())")
    List<PersonalSummaryVO> findDurationSummary(Long userId );

    /**
     * 查看单人开始到现在的总时长、总次数
     *
     * @param userId
     * @return
     */
    @Query(value = "select new com.sunlands.zlcx.usercenter.vo.PersonalTotalSummaryVO(coalesce(sum(learn.studyMinutes),0), count(learn.userId)) from com.sunlands.zlcx.usercenter.domain.UsersLearnTimingDO learn where learn.userId=?1")
    List<PersonalTotalSummaryVO> findTotalDurationSummary(Long userId );

    /**
     * 根据用户ID以及起始、结束日期获取用户每天的学习时长以及学习次数总和
     *
     * @param userId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select new com.sunlands.zlcx.usercenter.vo.PersonalSummaryDateVO(coalesce(sum( learn.studyMinutes),0),count( userId ) ,date(countTime)) \n" +
            "FROM com.sunlands.zlcx.usercenter.domain.UsersLearnTimingDO learn where learn.userId = ?1 and date(countTime) between ?2 and ?3 group by date(countTime) ORDER BY countTime desc ")
    List<PersonalSummaryDateVO> findDurationSummaryList(Long userId ,Date startTime , Date endTime);

    /**
     * 统计排行
     *
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "SELECT\n" +
            "\tlearn.user_id,\n" +
            "\tCOALESCE(sum(learn.study_minutes),0) sumMinutes,\n" +
            "\tcount( user_id ) frequency , date(count_time) dateTime \n" +
            "FROM\n" +
            "\ttz_learn_timing learn\n" +
            "WHERE\n" +
            "\t count_time  BETWEEN ?1 \n" +
            "\tAND ?2 \n" +
            "GROUP BY\n" +
            "\tlearn.user_id ORDER BY sumMinutes desc LIMIT 0 ,100" , nativeQuery = true)
    List<Object[]> getLeaderBoardDurationSummaryBy(Date startTime , Date endTime);

    @Query(value = "select * from tz_learn_timing WHERE user_id = ?1 and count_time >= ?2 and count_time < ?3 and status != -1 ", nativeQuery = true)
    List<UsersLearnTimingDO> findByUserIdAndBetweenDate(Long userId, Date start, Date end);

    Integer countByUserIdAndStatus(Long userId, Integer status);

    @Query(value = "select user_id,target_id, type, count_time, sum(study_minutes) sumMinutes, date_format(count_time, '%Y-%m-%d') yearMonthDay  from tz_learn_timing where user_id >310 and count_time >= ?1 and count_time < ?2  and type = ?3 group by user_id, type, date_format(count_time, '%Y-%m-%d') order by user_id, count_time, type asc", nativeQuery = true)
    List<Object[]> getOldDayLearnGroupByUserIdAndTypeAndCountTime(String startTime, String endTime, Integer type);


    UsersLearnTimingDO findFirstByUserIdOrderByCreateTimeDesc(Long userId);

    UsersLearnTimingDO findFirstByUserIdAndTypeAndEndTimeIsNullOrderByCreateTimeDesc(Long userId, Integer type);

    @Query(value = "select p.content_id, count(*) as count from tz_learn_timing p where  p.content_id in ?1 and p.`status`=1 and to_days(p.end_time) = to_days(now())  GROUP BY content_id ",nativeQuery = true)
    List<Object[]> findByContentIds(List<Long> coutentIds);

    @Query(value = "select new com.sunlands.zlcx.usercenter.vo.count.LearnTimeChartVO( p.contentId, '' , sum(p.studyMinutes))  " +
            "from com.sunlands.zlcx.usercenter.domain.UsersLearnTimingDO  p " +
            "where p.userId = ?1 and p.contentId is not null and p.endTime between ?2 and ?3  and p.status=1  " +
            "GROUP BY p.contentId ")
    List<LearnTimeChartVO> countByLearnContent(Long userId , Date startTime, Date endTime);

    @Query(value = "select new com.sunlands.zlcx.usercenter.vo.count.LearnTimeChartVO( date_format(p.endTime,'%Y-%m-%d'), sum(p.studyMinutes) )" +
            "from com.sunlands.zlcx.usercenter.domain.UsersLearnTimingDO  p " +
            "where p.userId = ?1 and p.contentId is not null and p.endTime between ?2 and ?3  and p.status=1  " +
            "GROUP BY date_format(p.endTime,'%Y-%m-%d') ")
    List<LearnTimeChartVO> countByDay(Long userId, Date startTime, Date endTime);

    @Query(value = "select new com.sunlands.zlcx.usercenter.vo.count.LearnTimeChartVO( date_format(p.endTime,'%Y-%m'), sum(p.studyMinutes) ) " +
            "from com.sunlands.zlcx.usercenter.domain.UsersLearnTimingDO  p " +
            "where p.userId = ?1 and p.contentId is not null and p.endTime between ?2 and ?3  and p.status=1  " +
            "GROUP BY date_format(p.endTime,'%Y-%m') ")
    List<LearnTimeChartVO> countByMonth(Long userId, Date startTime, Date endTime);


    @Query(value = "select *" +
            "from tz_learn_timing  p " +
            "where p.user_id = ?1 and p.content_id is not null and p.end_time between ?2 and ?3  and p.status=1  ", nativeQuery = true)
    List<UsersLearnTimingDO> listByDate(Long userId, Date startTime, Date endTime);

    @Query(value = "select new com.sunlands.zlcx.usercenter.vo.count.LearnTimeOverView( count(p.id), sum(p.studyMinutes), avg(p.studyMinutes))" +
            "from com.sunlands.zlcx.usercenter.domain.UsersLearnTimingDO  p " +
            "where p.userId = ?1 and p.contentId is not null and p.endTime between ?2 and ?3  and p.status=1  ")
    LearnTimeOverView countByUserId(Long userId, Date startTime, Date endTime);

    UsersLearnTimingDO findFirstByUserIdAndTypeNotOrderByCreateTimeDesc(Long userId, Integer type);
}

